package generator.util;

import generator.entity.ClassInfo;
import generator.entity.FieldInfo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author chengsp
 * @date 2019/9/5 13:59
 */
public class DatabaseUtil {
    private static Connection connection;
    private static Map<String, String> typeMapping = new HashMap<>();
    private static Map<String, String> driverMap = new HashMap<>();
    private static GenConfig config;

    private DatabaseUtil() {
    }

    public static Connection getConnection(GenConfig genConfig) {
        if (connection == null) {
            try {
                config = genConfig;
                if (config.getDriverName() == null || config.getDriverName().length() == 0){
                    config.setDriverName(driverMap.get(config.getDbType()));
                }
                Class.forName(genConfig.getDriverName());
                connection = DriverManager.getConnection(genConfig.getUrl(), genConfig.getUser(), genConfig.getPassword());
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return connection;
    }

    public static ClassInfo parseClassInfo(String tableName, GenConfig genConfig) throws SQLException {
        String dbType = genConfig.getDbType();
        String tableInfoSql = getTableInfoSql(tableName, genConfig.getDbName(), dbType);
        Statement statement = getConnection(genConfig).createStatement();
        if (statement == null) {
            throw new RuntimeException("不支持的数据库类型:" + dbType);
        }
        ResultSet tableResult = statement.executeQuery(tableInfoSql);
        ClassInfo classInfo = null;
        classInfo = new ClassInfo();
        classInfo.setTableName(tableName);
        String className = StringUtils.upperCaseFirst(StringUtils.underlineToCamelCase(tableName));
        classInfo.setClassName(className);
        classInfo.setClassComment(className);
        List<FieldInfo> fieldList = new ArrayList<>();
        while (tableResult.next()) {
            FieldInfo fieldInfo = new FieldInfo();
            fieldInfo.setColumnName(tableResult.getString(1));
            fieldInfo.setFieldClass(typeMapping.get(tableResult.getString(2)));
            String fieldName = StringUtils.underlineToCamelCase(tableResult.getString(1));
            fieldInfo.setFieldName(fieldName);
            fieldInfo.setFieldComment(tableResult.getString(3));
            fieldList.add(fieldInfo);
        }
        classInfo.setFieldList(fieldList);
        tableResult.close();
        statement.close();
        return classInfo;
    }

    private static String getTableInfoSql(String tableName, String dbName, String dbType) {
        String tableInfoSql = null;
        if ("mysql".equals(dbType)) {
            tableInfoSql = MessageFormat.format("select column_name,data_type,column_comment,numeric_precision," +
                    "numeric_scale,character_maximum_length,is_nullable nullable from information_schema.columns " +
                    "where table_name = \"{0}\" and table_schema = \"{1}\"", tableName, dbName);
        } else if ("oracle".equals(dbType)) {
            tableInfoSql = String.format(" select colstable.column_name column_name, colstable.data_type " +
                    "data_type, commentstable.comments column_comment, colstable.Data_Precision column_precision, " +
                    "colstable.Data_Scale column_scale,colstable.Char_Length,colstable.nullable from user_tab_cols " +
                    "colstable  inner join user_col_comments commentstable  on colstable.column_name = " +
                    "commentstable.column_name  where colstable.table_name = commentstable.table_name  and " +
                    "colstable.table_name = '%s'", tableName);
        } else if ("postgresql".equals(dbType)) {
            tableInfoSql = MessageFormat.format("SELECT a.attname AS  field,t.typname AS type,col_description(a" +
                    ".attrelid,a.attnum) as comment,null as column_precision,null as column_scale,null as " +
                    "Char_Length,a.attnotnull  FROM pg_class c,pg_attribute  a,pg_type t  WHERE c.relname = {0} and a" +
                    ".attnum > 0  and a.attrelid = c.oid and a.atttypid = t.oid  ORDER BY a.attnum ", tableName);
        } else if ("sqlserver".equals(dbType)) {
            tableInfoSql = MessageFormat.format("select distinct cast(a.name as varchar(50)) column_name,  cast(b" +
                    ".name as varchar(50)) data_type,  cast(e.value as varchar(200)) comment,  cast(ColumnProperty(a" +
                    ".object_id,a.Name,'''Precision''') as int) num_precision,  cast(ColumnProperty(a.object_id,a" +
                    ".Name,'''Scale''') as int) num_scale,  a.max_length,  (case when a.is_nullable=1 then '''y''' " +
                    "else '''n''' end) nullable,column_id   from sys.columns a left join sys.types b on a" +
                    ".user_type_id=b.user_type_id left join (select top 1 * from sys.objects where type = '''U''' and" +
                    " name ={0}  order by name) c on a.object_id=c.object_id left join sys.extended_properties e on e" +
                    ".major_id=c.object_id and e.minor_id=a.column_id and e.class=1 where c.name={0} order by a.column_id", dbName);
        }
        return tableInfoSql;
    }

    static {
        typeMapping.put("int", "Integer");
        typeMapping.put("char", "String");
        typeMapping.put("varchar", "String");
        typeMapping.put("datetime", "Date");
        typeMapping.put("timestamp", "Date");
        typeMapping.put("bit", "Integer");
        typeMapping.put("year", "Date");
        typeMapping.put("date", "Date");
        typeMapping.put("bigint", "Long");
        typeMapping.put("decimal", "BigDecimal");
        typeMapping.put("double", "Double");
        typeMapping.put("float", "Float");
        typeMapping.put("numeric", "Integer");
        typeMapping.put("text", "String");
        typeMapping.put("longtext", "String");
        typeMapping.put("time", "Date");
    }

    static {
        driverMap.put("oracle", "oracle.jdbc.driver.OracleDriver");
        driverMap.put("mysql", "com.mysql.jdbc.Driver");
        driverMap.put("postgresql", "org.postgresql.Driver");
        driverMap.put("sqlserver", "com.microsoft.sqlserver.jdbc.SQLServerDriver");
    }
}
